import { db } from '../index';
import { sql } from 'drizzle-orm';

/**
 * 数据库工具函数
 */

/**
 * 检查数据库连接
 */
export async function checkDatabaseConnection(): Promise<boolean> {
  try {
    await db.execute(sql`SELECT 1`);
    return true;
  } catch (error) {
    console.error('数据库连接失败:', error);
    return false;
  }
}

/**
 * 获取表的记录数量
 */
export async function getTableCount(tableName: string): Promise<number> {
  try {
    const result = await db.execute(sql`SELECT COUNT(*) as count FROM ${sql.identifier(tableName)}`);
    return Number(result[0]?.count || 0);
  } catch (error) {
    console.error(`获取表 ${tableName} 记录数量失败:`, error);
    return 0;
  }
}

/**
 * 清空表数据
 */
export async function truncateTable(tableName: string): Promise<void> {
  try {
    await db.execute(sql`DELETE FROM ${sql.identifier(tableName)}`);
    console.log(`✅ 清空表 ${tableName}`);
  } catch (error) {
    console.error(`清空表 ${tableName} 失败:`, error);
    throw error;
  }
}

/**
 * 检查表是否存在
 */
export async function tableExists(tableName: string): Promise<boolean> {
  try {
    const result = await db.execute(
      sql`SELECT name FROM sqlite_master WHERE type='table' AND name=${tableName}`
    );
    return result.length > 0;
  } catch (error) {
    console.error(`检查表 ${tableName} 是否存在失败:`, error);
    return false;
  }
}

/**
 * 获取数据库信息
 */
export async function getDatabaseInfo() {
  try {
    const tables = await db.execute(
      sql`SELECT name FROM sqlite_master WHERE type='table' ORDER BY name`
    );
    
    const info = {
      tables: tables.map(t => t.name),
      tableCount: tables.length,
      timestamp: new Date().toISOString()
    };
    
    return info;
  } catch (error) {
    console.error('获取数据库信息失败:', error);
    throw error;
  }
}

/**
 * 执行数据库备份（导出数据）
 */
export async function exportTableData(tableName: string) {
  try {
    const data = await db.execute(sql`SELECT * FROM ${sql.identifier(tableName)}`);
    return {
      table: tableName,
      count: data.length,
      data: data,
      exportedAt: new Date().toISOString()
    };
  } catch (error) {
    console.error(`导出表 ${tableName} 数据失败:`, error);
    throw error;
  }
}

/**
 * 数据库健康检查
 */
export async function healthCheck() {
  try {
    const isConnected = await checkDatabaseConnection();
    const dbInfo = await getDatabaseInfo();
    
    return {
      status: isConnected ? 'healthy' : 'unhealthy',
      connection: isConnected,
      database: dbInfo,
      checkedAt: new Date().toISOString()
    };
  } catch (error) {
    console.error('数据库健康检查失败:', error);
    return {
      status: 'error',
      connection: false,
      error: error instanceof Error ? error.message : '未知错误',
      checkedAt: new Date().toISOString()
    };
  }
}